{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## <font color=\"purple\"><h4 align=\"center\">Read/Write CSV and Excel Files in Pandas</font>"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"blue\">Read CSV</color>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIL</td>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers            eps  revenue price         people\n",
       "0   GOOGL          27.82       87   845     larry page\n",
       "1     WMT           4.61      484    65           n.a.\n",
       "2    MSFT             -1       85    64     bill gates\n",
       "3    RIL   not available       50  1023  mukesh ambani\n",
       "4    TATA            5.6       -1  n.a.     ratan tata"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "df = pd.read_csv(\"stock_data.csv\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GOOGL</th>\n",
       "      <th>27.82</th>\n",
       "      <th>87</th>\n",
       "      <th>845</th>\n",
       "      <th>larry page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RIL</td>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  GOOGL          27.82   87   845     larry page\n",
       "0   WMT           4.61  484    65           n.a.\n",
       "1  MSFT             -1   85    64     bill gates\n",
       "2  RIL   not available   50  1023  mukesh ambani\n",
       "3  TATA            5.6   -1  n.a.     ratan tata"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\", skiprows=1)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>GOOGL</th>\n",
       "      <th>27.82</th>\n",
       "      <th>87</th>\n",
       "      <th>845</th>\n",
       "      <th>larry page</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>RIL</td>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  GOOGL          27.82   87   845     larry page\n",
       "0   WMT           4.61  484    65           n.a.\n",
       "1  MSFT             -1   85    64     bill gates\n",
       "2  RIL   not available   50  1023  mukesh ambani\n",
       "3  TATA            5.6   -1  n.a.     ratan tata"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\", header=1) # skiprows and header are kind of same\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ticker</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>tickers</th>\n",
       "      <td>eps</td>\n",
       "      <td>revenue</td>\n",
       "      <td>price</td>\n",
       "      <td>people</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GOOGL</th>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMT</th>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MSFT</th>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>RIL</th>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TATA</th>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                ticker      eps revenue         people\n",
       "tickers            eps  revenue   price         people\n",
       "GOOGL            27.82       87     845     larry page\n",
       "WMT               4.61      484      65           n.a.\n",
       "MSFT                -1       85      64     bill gates\n",
       "RIL      not available       50    1023  mukesh ambani\n",
       "TATA               5.6       -1    n.a.     ratan tata"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\", header=None, names = [\"ticker\",\"eps\",\"revenue\",\"people\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers    eps  revenue  price      people\n",
       "0   GOOGL  27.82       87    845  larry page\n",
       "1     WMT   4.61      484     65        n.a."
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\",  nrows=2)\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false,
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845.0</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>85</td>\n",
       "      <td>64.0</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50</td>\n",
       "      <td>1023.0</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.60</td>\n",
       "      <td>-1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers    eps  revenue   price         people\n",
       "0   GOOGL  27.82       87   845.0     larry page\n",
       "1     WMT   4.61      484    65.0            NaN\n",
       "2    MSFT  -1.00       85    64.0     bill gates\n",
       "3    RIL     NaN       50  1023.0  mukesh ambani\n",
       "4    TATA   5.60       -1     NaN     ratan tata"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\", na_values=[\"n.a.\", \"not available\"])\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87.0</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484.0</td>\n",
       "      <td>65</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1.00</td>\n",
       "      <td>85.0</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIL</td>\n",
       "      <td>NaN</td>\n",
       "      <td>50.0</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.60</td>\n",
       "      <td>NaN</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers    eps  revenue price         people\n",
       "0   GOOGL  27.82     87.0   845     larry page\n",
       "1     WMT   4.61    484.0    65            NaN\n",
       "2    MSFT  -1.00     85.0    64     bill gates\n",
       "3    RIL     NaN     50.0  1023  mukesh ambani\n",
       "4    TATA   5.60      NaN  n.a.     ratan tata"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv(\"stock_data.csv\",  na_values={\n",
    "        'eps': ['not available'],\n",
    "        'revenue': [-1],\n",
    "        'people': ['not available','n.a.']\n",
    "    })\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"blue\">Write to CSV</color>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"new.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['tickers', 'eps', 'revenue', 'price', 'people'], dtype='object')"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"new.csv\",header=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df.to_csv(\"new.csv\", columns=[\"tickers\",\"price\"], index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"blue\">Read Excel</color>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>n.a.</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIL</td>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>n.a.</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers            eps  revenue price         people\n",
       "0   GOOGL          27.82       87   845     larry page\n",
       "1     WMT           4.61      484    65           n.a.\n",
       "2    MSFT             -1       85    64     bill gates\n",
       "3    RIL   not available       50  1023  mukesh ambani\n",
       "4    TATA            5.6       -1  n.a.     ratan tata"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_excel(\"stock_data.xlsx\",\"Sheet1\")\n",
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>tickers</th>\n",
       "      <th>eps</th>\n",
       "      <th>revenue</th>\n",
       "      <th>price</th>\n",
       "      <th>people</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GOOGL</td>\n",
       "      <td>27.82</td>\n",
       "      <td>87</td>\n",
       "      <td>845</td>\n",
       "      <td>larry page</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>WMT</td>\n",
       "      <td>4.61</td>\n",
       "      <td>484</td>\n",
       "      <td>65</td>\n",
       "      <td>Sam Walton</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>MSFT</td>\n",
       "      <td>-1</td>\n",
       "      <td>85</td>\n",
       "      <td>64</td>\n",
       "      <td>bill gates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>RIL</td>\n",
       "      <td>not available</td>\n",
       "      <td>50</td>\n",
       "      <td>1023</td>\n",
       "      <td>mukesh ambani</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TATA</td>\n",
       "      <td>5.6</td>\n",
       "      <td>-1</td>\n",
       "      <td>50</td>\n",
       "      <td>ratan tata</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  tickers            eps  revenue  price         people\n",
       "0   GOOGL          27.82       87    845     larry page\n",
       "1     WMT           4.61      484     65     Sam Walton\n",
       "2    MSFT             -1       85     64     bill gates\n",
       "3    RIL   not available       50   1023  mukesh ambani\n",
       "4    TATA            5.6       -1     50     ratan tata"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def convert_people_cell(cell):\n",
    "    if cell==\"n.a.\":\n",
    "        return 'Sam Walton'\n",
    "    return cell\n",
    "\n",
    "def convert_price_cell(cell):\n",
    "    if cell==\"n.a.\":\n",
    "        return 50\n",
    "    return cell\n",
    "    \n",
    "df = pd.read_excel(\"stock_data.xlsx\",\"Sheet1\", converters= {\n",
    "        'people': convert_people_cell,\n",
    "        'price': convert_price_cell\n",
    "    })\n",
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### <font color=\"blue\">Write to Excel</color>"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {
    "collapsed": false,
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "df.to_excel(\"new.xlsx\", sheet_name=\"stocks\", index=False, startrow=2, startcol=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "**Write two dataframes to two separate sheets in excel**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "df_stocks = pd.DataFrame({\n",
    "    'tickers': ['GOOGL', 'WMT', 'MSFT'],\n",
    "    'price': [845, 65, 64 ],\n",
    "    'pe': [30.37, 14.26, 30.97],\n",
    "    'eps': [27.82, 4.61, 2.12]\n",
    "})\n",
    "\n",
    "df_weather =  pd.DataFrame({\n",
    "    'day': ['1/1/2017','1/2/2017','1/3/2017'],\n",
    "    'temperature': [32,35,28],\n",
    "    'event': ['Rain', 'Sunny', 'Snow']\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "with pd.ExcelWriter('stocks_weather.xlsx') as writer:\n",
    "    df_stocks.to_excel(writer, sheet_name=\"stocks\")\n",
    "    df_weather.to_excel(writer, sheet_name=\"weather\")"
   ]
  }
 ],
 "metadata": {
  "anaconda-cloud": {},
  "kernelspec": {
   "display_name": "Python [conda root]",
   "language": "python",
   "name": "conda-root-py"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 1
}
